package com.houduan.dao;

import com.houduan.model.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    private static final String URL = "jdbc:mysql://localhost:3306/";
    private static final String DATABASE_NAME = "netbar";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "rjxy123456";

    private static final String INSERT_USER_SQL = "INSERT INTO users (username, password, email, phone, gender, balance) VALUES (?, ?, ?, ?, ?, ?)";
    private static final String SELECT_USER_BY_ID = "SELECT * FROM users WHERE id = ?";
    private static final String SELECT_ALL_USERS = "SELECT * FROM users";
    private static final String DELETE_USER_SQL = "DELETE FROM users WHERE id = ?";
    private static final String UPDATE_USER_SQL = "UPDATE users SET username = ?, password = ?, email = ?, phone = ?, gender = ?, balance = ? WHERE id = ?";

    // 初始化数据库（如果不存在，则创建数据库和表）
    public UserDao() {
        try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement statement = connection.createStatement()) {

            // 检查并创建数据库
            String createDatabaseSQL = "CREATE DATABASE IF NOT EXISTS " + DATABASE_NAME;
            statement.executeUpdate(createDatabaseSQL);

            // 使用创建的数据库
            String useDatabaseSQL = "USE " + DATABASE_NAME;
            statement.executeUpdate(useDatabaseSQL);

            // 创建用户表（如果不存在）
            String createTableSQL = "CREATE TABLE IF NOT EXISTS users (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY, " +
                    "username VARCHAR(255) NOT NULL, " +
                    "password VARCHAR(255) NOT NULL, " +
                    "email VARCHAR(255), " +
                    "phone VARCHAR(50), " +
                    "gender VARCHAR(10), " +
                    "balance DOUBLE DEFAULT 0.0)";
            statement.executeUpdate(createTableSQL);

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("DB初始化错误" + e.getMessage());
        }
    }

    // 获取数据库连接
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL + DATABASE_NAME, USERNAME, PASSWORD);
    }

    // 增加用户
    public void addUser(User user) {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USER_SQL)) {
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getPhone());
            preparedStatement.setString(5, user.getGender());
            preparedStatement.setDouble(6, user.getBalance());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取所有用户
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_USERS);
             ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String email = resultSet.getString("email");
                String phone = resultSet.getString("phone");
                String gender = resultSet.getString("gender");
                double balance = resultSet.getDouble("balance");
                users.add(new User(id, username, password, email, phone, gender, balance));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    // 根据 ID 获取用户
    public User getUserById(int id) {
        User user = null;
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_USER_BY_ID)) {
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String email = resultSet.getString("email");
                String phone = resultSet.getString("phone");
                String gender = resultSet.getString("gender");
                double balance = resultSet.getDouble("balance");
                user = new User(id, username, password, email, phone, gender, balance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    public List<User> searchUsers(String query) {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users WHERE username LIKE ? OR id = ?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

            // 设置模糊查询参数
            preparedStatement.setString(1, "%" + query + "%");

            // 尝试将 query 转换为整数，如果失败，设置为无效值
            int queryId = -1; // 默认值为无效 ID
            try {
                queryId = Integer.parseInt(query);
            } catch (NumberFormatException e) {
                // query 不是数字，仅用于模糊匹配用户名
                System.out.println("Query is not an integer, treating as username: " + query);
            }
            preparedStatement.setInt(2, queryId);

            // 输出调试信息
            System.out.println("Executing SQL: " + sql);
            System.out.println("With parameters: username LIKE '%" + query + "%', id = " + queryId);

            // 执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            // 遍历结果集并封装为 User 对象
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setEmail(resultSet.getString("email"));
                user.setPhone(resultSet.getString("phone"));
                user.setGender(resultSet.getString("gender"));
                user.setBalance(resultSet.getDouble("balance"));
                users.add(user); // 添加到用户列表
            }

            // 打印查询结果数量
            System.out.println("Search results found: " + users.size());

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Error occurred while searching users: " + e.getMessage());
        }
        return users;
    }

    // 删除用户
    public void deleteUser(int id) {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_USER_SQL)) {
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 更新用户
    public void updateUser(User user) {
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_USER_SQL)) {
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getPassword());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getPhone());
            preparedStatement.setString(5, user.getGender());
            preparedStatement.setDouble(6, user.getBalance());
            preparedStatement.setInt(7, user.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void updateBalance(int userId, double amount) {
        String updateBalanceSql = "UPDATE users SET balance = balance + ? WHERE id = ?";
        try (Connection connection = getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(updateBalanceSql)) {
            preparedStatement.setDouble(1, amount);
            preparedStatement.setInt(2, userId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
